//201902104030_何琛选
package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Teacher;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class TeacherDao {
    private static TeacherDao teacherDao = null;
    private static Collection<Teacher> teachers;

    private TeacherDao() {
    }

    public static TeacherDao getInstance() {
        if (teacherDao == null){
            teacherDao = new TeacherDao();
        }
        return teacherDao;
    }
    public int count(Connection connection) throws SQLException{
        return CommonDao.getInstance().count(connection,"teacher");
    }

    public Collection<Teacher> findAll(
            Connection connection,
            List<Condition> conditionList_json_str,
            Pagination pagination)
            throws SQLException{
        //创建Teacher类型集合类对象,HashSet不保证结果中元素的顺序
        Collection<Teacher> desiredTeachers = new HashSet<Teacher>();

        int totalNum = TeacherDao.getInstance().count(connection);
        //创建查询教师的主语句
        StringBuilder teacher_sql = new StringBuilder("select * from Teacher");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList_json_str != null){
            String whereClause = Condition.toWhereClause(conditionList_json_str);
            teacher_sql.append(whereClause);
        }
        //如果参数不为空，为SQL语句添加LIMIT子句
        if (pagination != null){
            teacher_sql.append(pagination.toLimitClause(totalNum) + " ");
        }
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt_select_all= connection.prepareStatement(teacher_sql.toString());
        //遍历结果集，根据元组创建对象，并添加到集合类对象中
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        //遍历resultSet
        while (resultSet_select_all.next()) {
            int id = resultSet_select_all.getInt("id");
            String name = resultSet_select_all.getString("name");
            String no = resultSet_select_all.getString("no");

            Teacher desiredTeacher = new Teacher(name,no);
            desiredTeacher.setId(id);
            desiredTeachers.add(desiredTeacher);

        }
        JdbcHelper.close(resultSet_select_all, pstmt_select_all);
        return  desiredTeachers;

    }

    public Teacher find(Integer id,Connection connection) throws SQLException {
        Teacher desiredTeacher = null;
        //Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String findTeacher_sql = "select * from teacher where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(findTeacher_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        if (resultSet.next()){
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");

            desiredTeacher = new Teacher(id,name,no,null,null,null);
        }
        //关闭连接
        JdbcHelper.close(resultSet,pstmt);
        //返回school对象
        return desiredTeacher;
    }

    public boolean update(Teacher teacher,Connection connection) throws SQLException{
        String updateTeaacher_sql = "UPDATE Teacher " +
                "SET name = ?,no = ?,profTitle_id=?,department_id,degree_id " +
                "WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement preparedStatement = connection.prepareStatement(updateTeaacher_sql);
        preparedStatement.setString(1,teacher.getName());
        preparedStatement.setString(2,teacher.getNo());
        preparedStatement.setInt(3,teacher.getTitle().getId());
        //preparedStatement.setInt(4,teacher.getUser().getId());
        preparedStatement.setInt(4,teacher.getDepartment().getId());
        preparedStatement.setInt(5,teacher.getDegree().getId());
        preparedStatement.setInt(6, teacher.getId());

        //修改teacher 并判断是否修改成功
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);

        return affectedRowNum>0;
    }

    public int add(Teacher teacher, Connection connection) throws SQLException{
        PreparedStatement preparedStatement = connection.prepareStatement
                ("INSERT INTO teacher" +
                                "(name ,no)" +
                                "VALUE (?,?)",
                        //要求返回主键集合
                        Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1,teacher.getName());
        preparedStatement.setString(2,teacher.getNo());
        //执行预编译语句，用其返回值、影响的行数为赋值affectedRowNum
        int affectedRowNum = preparedStatement.executeUpdate();
        int idOfAddTeacher = 0;
        //获得生成的主键集合
        ResultSet resultSet = preparedStatement.getGeneratedKeys();
        if (resultSet.next()){
            //读取第一个主键
            idOfAddTeacher = resultSet.getInt(1);
        }
        return idOfAddTeacher;
    }

    public boolean delete(Integer id,Connection connection) throws SQLException{
        //创建sql语句
        String deleteTeacher_sql = "delete from teacher where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteTeacher_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);

        return pstmt.executeLargeUpdate()>0;
    }

    public boolean delete(Teacher teacher,Connection connection) throws SQLException{
        String deleteTeacher_sql = "delete from teacher where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteTeacher_sql);
        //为预编译参数赋值
        pstmt.setInt(1,teacher.getId());
        //根据id删除学院 并判断是否删除成功
        return pstmt.executeUpdate()>0;
    }
}
